using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Dynamic;
using System.Data.SqlClient;
using System.Configuration;
using Model;
using Common;

namespace DAL
{
    public class JiepianDAL
    {
        public static int Save(JiepianInfo info)
        {
            string sql = string.Format("insert into [jiepian]([JPName],[UCode],[AName],[BPCount],[LKCount],[PID],[JPDate],[Remarks],[Mobile],[Imgs],[NumBer],[CreateDate]) values(@JPName,@UCode,@AName,@BPCount,@LKCount,@PID,@JPDate,@Remarks,@Mobile,@Imgs,@NumBer,@CreateDate);select @@identity;");
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@JPName", info.JPName),
                new SqlParameter("@UCode", info.UCode),
                new SqlParameter("@AName", info.AName),
                new SqlParameter("@BPCount", info.BPCount),
                new SqlParameter("@LKCount", info.LKCount),
                new SqlParameter("@PID", info.PID),
                new SqlParameter("@JPDate", info.JPDate),
                new SqlParameter("@Remarks", info.Remarks),
                new SqlParameter("@Mobile",info.Mobile),
                new SqlParameter("@Imgs", info.Imgs),
                new SqlParameter("@NumBer",info.NumBer),
                new SqlParameter("@CreateDate", info.CreateDate)
            };
            return DataConvert.ToInt(CommonDAL.ExecuteScalar(sql, paras));
        }
        public static int Update(JiepianInfo info)
        {
            string sql = string.Format("update [jiepian] set [JPName]=@JPName,[UCode]=@UCode,[AName]=@AName,[BPCount]=@BPCount,[LKCount]=@LKCount,[PID]=@PID,[JPDate]=@JPDate,[Remarks]=@Remarks,[Imgs]=@Imgs where [ID]=@ID");
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@JPName", info.JPName),
                new SqlParameter("@UCode", info.UCode),
                new SqlParameter("@AName", info.AName),
                new SqlParameter("@BPCount", info.BPCount),
                new SqlParameter("@LKCount", info.LKCount),
                new SqlParameter("@PID", info.PID),
                new SqlParameter("@JPDate", info.JPDate),
                new SqlParameter("@Remarks", info.Remarks),
                new SqlParameter("@Imgs", info.Imgs),
                new SqlParameter("@ID", info.ID)
            };
            return CommonDAL.ExecuteNoneQuery(sql, paras) ? info.ID : 0;
        }
        public static JiepianInfo FindOneByID(int id)
        {
            DataRow dr = CommonDAL.FindOneByID(id, "[jiepian]");
            return Simple_Data(dr);
        }
        public static List<JiepianInfo> FindListByIDs(string ids)
        {
            DataTable dt = CommonDAL.FindListByIDs(ids, "[jiepian]");
            List<JiepianInfo> list = new List<JiepianInfo>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(Simple_Data(row));
            }
            return list;
        }
        public static List<JiepianInfo> FindPage(string keyword, string begindate, string enddate, int startindex, int pagesize, string order, string desc, out int count)
        {
            StringBuilder filter = new StringBuilder();
            if (!string.IsNullOrEmpty(keyword))
            {
                filter.AppendFormat(" and [jiepian].Name like '%{0}%'", keyword);
            }
            if (!string.IsNullOrEmpty(begindate) && !string.IsNullOrEmpty(enddate))
            {
                filter.AppendFormat(" and [jiepian].CreateDate between '{0}' and '{1}'", DataConvert.ToDateTime(begindate).ToString("yyyy-MM-dd 00:00:00"), DataConvert.ToDateTime(enddate).ToString("yyyy-MM-dd 23:59:59"));
            }
            DataTable dt = CommonDAL.FindPage(startindex, pagesize, order, desc, filter, "[jiepian]", out count);
            List<JiepianInfo> list = new List<JiepianInfo>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(Simple_Data(row));
            }
            return list;
        }
        public static List<JiepianInfo> FindAll()
        {
            DataTable dt = CommonDAL.GetDataTable(string.Format("select * from [jiepian]"));
            List<JiepianInfo> list = new List<JiepianInfo>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(Simple_Data(row));
            }
            return list;
        }
        public static List<JiepianInfo> FindListByUCode(string ucode,int rawid)
        {
            DataTable dt = new DataTable();
            if (!string.IsNullOrEmpty(ucode))
            {
                dt = CommonDAL.GetDataTable(string.Format("select * from [jiepian] where UCode='" + ucode + "'"));
            }
            else {
                dt = CommonDAL.GetDataTable(string.Format("select * from [jiepian] where NumBer='" + rawid + "'"));
            }
            
            List<JiepianInfo> list = new List<JiepianInfo>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(Simple_Data(row));
            }
            return list;
            //DataRow dr = CommonDAL.GetDataRow(string.Format("select * from [jiepian] where UCode='" + ucode + "'"));
            //return Simple_Data(dr);
        }
        public static JiepianInfo FindOneByUCode(String ucode)
        {           
           DataRow dr = CommonDAL.GetDataRow(string.Format("select * from [jiepian] where UCode='" + ucode + "'"));
           return Simple_Data(dr);
        }

        public static JiepianInfo FindOneByUCodeAndNumber(string ucode, string number)
        {
            string sql = "";
            if (!string.IsNullOrEmpty(ucode))
            {
                sql = string.Format("select top 1 * from [jiepian] where UCode='" + ucode + "' order by ID desc ");
            }
            else
            {
                sql = string.Format("select top 1 * from [jiepian] where NumBer='" + number + "' order by ID desc");
            }
            DataRow dr = CommonDAL.GetDataRow(sql);
            return Simple_Data(dr);
        }

        public static int UpdateImgs(string paths, int id)
        {
            string sql = string.Format("select Imgs from [jiepian] where ID={0}", id);
            string isnull = CommonDAL.ExecuteScalar(sql).ToString();
            if (isnull == "" || isnull == null)
            {
                string sql1 = string.Format("update [jiepian] set [Imgs]=@Imgs where [ID]=@ID");
                SqlParameter[] paras = new SqlParameter[]{               
                    new SqlParameter("@Imgs", paths),
                    new SqlParameter("@ID", id)
                };
                return CommonDAL.ExecuteNoneQuery(sql1, paras) ? id : 0;
            }
            else
            {
                string sql2 = string.Format("update [jiepian] set [Imgs]=[Imgs]+','+@Imgs where [ID]=@ID");
                SqlParameter[] paras = new SqlParameter[]{               
                    new SqlParameter("@Imgs", paths),
                    new SqlParameter("@ID", id)
                };
                return CommonDAL.ExecuteNoneQuery(sql2, paras) ? id : 0;
            }
        }

        public static bool DeleteImgsByID(string path, int id)
        {
            string sql = string.Format(@"update [jiepian] set Imgs = REPLACE(Imgs,Imgs,','+Imgs) where ID={0};
                                         update [jiepian] set Imgs = REPLACE(Imgs,',{1}','') where ID={0};", id, path);
            bool count = CommonDAL.ExecuteNoneQuery(sql);
            string sql1 = string.Format("select Imgs from [jiepian] where ID={0}", id);
            string isnull = CommonDAL.ExecuteScalar(sql1).ToString();
            if (isnull != "" && isnull != null)
            {
                string sql2 = string.Format("update [jiepian] set Imgs = (select substring(Imgs,2,len(Imgs)-1) from [jiepian] where ID={0}) where ID={0}", id);
                count = CommonDAL.ExecuteNoneQuery(sql2);
            }
            return count;
        }

        private static JiepianInfo Simple_Data(DataRow dr)
        {
            JiepianInfo info = new JiepianInfo();
            if (dr != null)
            {
                info.ID = DataConvert.ToInt(dr["ID"]);
                info.JPName = DataConvert.ToString(dr["JPName"]);
                info.UCode = DataConvert.ToString(dr["UCode"]);
                info.AName = DataConvert.ToString(dr["AName"]);
                info.BPCount = DataConvert.ToInt(dr["BPCount"]);
                info.LKCount = DataConvert.ToInt(dr["LKCount"]);
                info.PID = DataConvert.ToInt(dr["PID"]);
                PositionInfo poinfo = PositionDAL.FindOneByID(info.PID);
                info.PIDText = poinfo.Code;

                info.JPDate = DataConvert.ToDateTime(dr["JPDate"]);
                info.Remarks = DataConvert.ToString(dr["Remarks"]);
                info.Mobile = DataConvert.ToString(dr["Mobile"]);
                info.Imgs = DataConvert.ToString(dr["Imgs"]);
                info.NumBer = DataConvert.ToInt(dr["NumBer"]);
                info.CreateDate = DataConvert.ToDateTime(dr["CreateDate"]);
            }
            return info;
        }
    }
}

